import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.concurrent.CopyOnWriteArrayList;

import processing.core.PApplet;


public class IssuePlayerProject {
	private Connection conn = null;
	private Statement s = null;
	private ResultSet rs = null;
	
	private boolean projectOpen = false;
	private int projectId = 0;
	private String projectName = "";
	
	public boolean isSQLOpen() {
		if(conn != null && rs != null)
			return true;
		
		return false;
	}
	
	public boolean openSQLConnection(String domain, String username, String password) {
		try {		
        	conn = DriverManager.getConnection( "jdbc:mysql://"+domain+"/issueplayer?user="+username+"&password="+password);
        	s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);        	
        	return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}		
	}
	
	public boolean closeSQLConnection() {
		try {		
			rs.close();
			rs = null;
			s.close();
			s = null;
			conn.close();
			conn = null;        	
        	return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}		
	}

	public ArrayList<String> getProjectList() {
		if(conn == null || s == null)
			return null;
		
		try {
			rs = s.executeQuery("SELECT project_name FROM project");
			ArrayList<String> pList = new ArrayList<String>();
			
			for(rs.first(); !rs.isAfterLast(); rs.next())
				pList.add(rs.getString("project_name"));
			
			return pList;
			
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return null;
		}		
	}

	public long getStartTime() {
		if(conn == null || s == null)
			return 0;
		
		try {
			rs = s.executeQuery("SELECT register_time FROM project WHERE project_id="+projectId);
			if(rs.first())
				return rs.getLong("register_time")*1000;
			else
				return 0;
			
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return 0;
		}		
	}

	public int getMaxTime() {
		return daysBetween(new Date(getStartTime()), new Date());
	}
		
	public String getProjectName() {
		return projectName;
	}

	public boolean isProjectOpen() {
		return projectOpen;
	}
	
	public boolean openProject(String name) {
		try {
			rs = s.executeQuery("SELECT project_id FROM project WHERE project_name=\""+name+"\"");
			
			if(!rs.first())
				return false;
			
			projectId = rs.getInt("project_id");
			projectName = name;
			projectOpen = true;
			return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}		
	}

	public boolean closeProject() {
		projectOpen = false;
		return true;
	}

	public boolean loadUsers(CopyOnWriteArrayList<User> colSubmittedBy, CopyOnWriteArrayList<User> colAssignedTo, PApplet parent) {
		try {
			String select = "DISTINCT U.user_id, U.user_name, U.realname, U.status";
			String from = "issue_project_list IPL, issue ISS, user U";
			String where = "IPL.project_issue_id=ISS.project_issue_id AND ISS.submitted_by=U.user_id AND IPL.project_id="+projectId;

			rs = s.executeQuery("SELECT "+select+" FROM "+from+" WHERE "+where);

			for(rs.first(); !rs.isAfterLast(); rs.next()) {
				User u = new User(parent);
				u.setId(rs.getInt("user_id"));
				u.setStatus(rs.getString("status") == "A" ? 1 : 0);
				u.setUserName(rs.getString("user_name"));
				u.setRealName(rs.getString("realname"));
				colSubmittedBy.add(u);
			}
		
			where = "IPL.project_issue_id=ISS.project_issue_id AND ISS.assigned_to=U.user_id AND IPL.project_id="+projectId;

			rs = s.executeQuery("SELECT "+select+" FROM "+from+" WHERE "+where);

			for(rs.first(); !rs.isAfterLast(); rs.next()) {
				User u = new User(parent);
				u.setId(rs.getInt("user_id"));
				u.setStatus(rs.getString("status") == "A" ? 1 : 0);
				u.setUserName(rs.getString("user_name"));
				u.setRealName(rs.getString("realname"));
				colAssignedTo.add(u);
			}

			return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}		
	}

	public boolean loadIssues(CopyOnWriteArrayList<Issue> colI, CopyOnWriteArrayList<User> colSubmittedBy, CopyOnWriteArrayList<User> colAssignedTo, PApplet parent, Tooltip tooltip, Priority priority) {
		try {
			long startTime = getStartTime();
			
			String select = "DISTINCT ISS.issue_id, ISS.status_id, ISS.resolution_id, ISS.category_id, ISS.project_issue_id, ISS.priority, ISS.submitted_by, ISS.assigned_to, ISS.open_date, ISS.close_date";
			String from = "issue_project_list IPL, issue ISS";
			String where = "IPL.project_issue_id=ISS.project_issue_id AND IPL.project_id="+projectId;

			rs = s.executeQuery("SELECT "+select+" FROM "+from+" WHERE "+where);

			for(rs.first(); !rs.isAfterLast(); rs.next()) {
				Issue iss = new Issue(parent, tooltip, priority);
				iss.setId(rs.getInt("issue_id"));
				iss.setStatus(rs.getInt("status_id"));
				iss.setResolution(rs.getInt("resolution_id"));
				iss.setProjectIssueId(rs.getInt("project_issue_id"));
				iss.setCategory(rs.getInt("category_id"));
				iss.setPriority(rs.getInt("priority"));
				for(int i = 0; i < colSubmittedBy.size(); i++) {
					if(colSubmittedBy.get(i).getId() == rs.getInt("submitted_by"))
						iss.setSubmittedBy(colSubmittedBy.get(i));
				}
				for(int i = 0; i < colAssignedTo.size(); i++) {
					if(colAssignedTo.get(i).getId() == rs.getInt("assigned_to"))
						iss.setAssignedTo(colAssignedTo.get(i));
				}
				long open_date = rs.getLong("open_date")*1000;
				if(open_date == 0)
					iss.setOpenDate(0);
				else
					iss.setOpenDate(daysBetween(new Date(startTime), new Date(open_date)));

				long close_date = rs.getLong("close_date")*1000;
				if(close_date == 0)
					iss.setCloseDate(0);
				else
					iss.setCloseDate(daysBetween(new Date(startTime), new Date(close_date)));

				colI.add(iss);
			}

			return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}
	}

	public boolean loadCategories(CategoryBins bins) {
		try {
			String select = "DISTINCT IPL.project_issue_id, IPL.name, IPL.description";
			String from = "issue_project_list IPL";
			String where = "IPL.project_id="+projectId;

			rs = s.executeQuery("SELECT "+select+" FROM "+from+" WHERE "+where);

			ArrayList<Integer> id = new ArrayList<Integer>();
			ArrayList<String> name = new ArrayList<String>();
			ArrayList<String> desc = new ArrayList<String>();
			
			for(rs.first(); !rs.isAfterLast(); rs.next()) {
				id.add(rs.getInt("project_issue_id"));
				name.add(rs.getString("name"));
				desc.add(rs.getString("description"));
			}

			bins.loadBins(id, name, desc);
			
			return true;
		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return false;
		}
	}

	public String getDetails(Issue iss) {
		try {
			String select = "ISS.details";
			String from = "issue ISS";
			String where = "ISS.issue_id="+iss.getId();

			rs = s.executeQuery("SELECT "+select+" FROM "+from+" WHERE "+where);

			rs.first();
			
			if(rs.isAfterLast())
				return "";
			
			return rs.getString("details");

		} catch (SQLException ex) {
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		    return "";
		}
	}
	
	@SuppressWarnings("deprecation")
	private int daysBetween(Date d1, Date d2) {
		int[] months = {0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334};
		return Math.abs( (d1.getYear()*365+months[d1.getMonth()]+d1.getDate()) - (d2.getYear()*365+months[d2.getMonth()]+d2.getDate()) );
	}
}
